//prepare other NLSY wealth measures
import delimited "$data/NLSY97/wealth_grants/wealth_grants.csv", clear
ren *, upper
do "$data/NLSY97/wealth_grants/wealth_grants-value-labels"
ren *, lower
drop key* cv_sample
ren pubid uniqid
drop ysch*

//renaming
ren p5_115 owe_mortgage
ren p5_118 owe_mortgage_other
ren p5_128 total_tuition_prepaid
ren p5_131 total_pension
ren p5_134 total_checking
ren p5_137 total_bonds
ren p5_140 total_stocks
ren p5_143 total_vehicles
ren p5_145 owe_vehicles
ren p5_149 total_other
ren p5_152 owe_child
ren p5_155 owe_other


ds uniqid, not
foreach var in `r(varlist)'{
	replace `var' = 0 if `var' == -4
	replace `var' = . if `var' == -1 | `var' == -2
	drop if `var'==.
}

save "$temp/nlsy_parent_wealth", replace

//other wealth
import delimited "$data/NLSY97/wealth_other/wealth_other.csv", clear
ren *, upper
do "$data/NLSY97/wealth_other/wealth_other-value-labels"
ren *, lower
drop key* cv_sample
ren pubid uniqid
ren p5_121 total_business
ren p5_125 total_real_estate

ds uniqid, not
foreach var in `r(varlist)'{
	replace `var' = 0 if `var' == -4
	replace `var' = . if `var' == -1 | `var' == -2
	drop if `var'==.
}

merge 1:1 uniqid using "$temp/nlsy_parent_wealth", keep(match) nogen
save "$temp/nlsy_parent_wealth_all", replace

//a few improvements
use "$temp/nlsy_base_sample", clear

//parent income/wealth quintiles
drop if parent_inc == . | parent_house == . //require that parent income/wealth be observed
merge 1:1 uniqid using "$temp/nlsy_parent_wealth_all", keep(match) nogen

//child ability/parent income decile distribution
//drop ability
xtile ability2 = score [fw = weight_1997], nq(3) //ability tertiles
xtile parent_inc_decile = parent_income [w = weight_1997], nq(10) //parent income deciles
xtile parent_inc_ventile = parent_income [w = weight_1997], nq(20) //parent income ventiles
tab ability2 parent_inc_ventile [w = weight_1997], row



//ability mean/SDs based on parent income ventile
forval i = 1/20{
	su ability_pct if parent_inc_ventile == `i'
	local abil_mean_`i' = `r(mean)'
	local abil_sd_`i' = `r(sd)'
}


gen own_house = (parent_house_value!=0)
replace parent_house_value = 10000 if parent_house_value<10000 & parent_house_value!=0

//construct house equity and non-housign wealth variables
gen parent_house_equity = parent_house_value - owe_mortgage - owe_mortgage_other
//gen total_wealth_nonhouse = total_tuition_prepaid + total_pension + total_checking + total_bonds + total_stocks + ///
//gen total_wealth_nonhouse = total_tuition_prepaid + total_checking + total_bonds + total_stocks + ///
gen total_wealth_nonhouse = total_tuition_prepaid + total_checking + total_bonds + total_stocks + ///
total_vehicles + total_other + total_business + total_real_estate - owe_vehicles - owe_child - owe_other

//check: lower-income parents have higher share of wealth in houses
gen total_wealth = parent_house_equity + total_wealth_nonhouse
gen frac_house = parent_house_equity/total_wealth
su frac_house if own_house & parent_inc_dec == 1 //check!

//deflate and normalize
replace parent_house_equity = parent_house_equity / 0.74623
replace parent_house_equity = parent_house_equity/40000
replace total_wealth_nonhouse = total_wealth_nonhouse / 0.74623
replace total_wealth_nonhouse = total_wealth_nonhouse/40000


su total_wealth_nonhouse if parent_inc_ventile == 1
su total_wealth_nonhouse if parent_inc_ventile == 5
su total_wealth_nonhouse if parent_inc_ventile == 10
su total_wealth_nonhouse if parent_inc_ventile == 15
su total_wealth_nonhouse if parent_inc_ventile == 18
su total_wealth_nonhouse if parent_inc_ventile == 19
su total_wealth_nonhouse if parent_inc_ventile == 20


su parent_house_equity if parent_inc_ventile == 1 & own_house
su parent_house_equity if parent_inc_ventile == 5 & own_house
su parent_house_equity if parent_inc_ventile == 10 & own_house
su parent_house_equity if parent_inc_ventile == 15 & own_house
su parent_house_equity if parent_inc_ventile == 18 & own_house
su parent_house_equity if parent_inc_ventile == 19 & own_house
su parent_house_equity if parent_inc_ventile == 20 & own_house




replace total_wealth = total_wealth / 0.74623
replace total_wealth = total_wealth/40000
replace parent_income = parent_income / 0.74623
replace parent_income = parent_income/40000

//compute distributions. Now do log normals!
forval i = 1/20{
	su own_house [w = weight_1997] if parent_inc_ventile == `i'
	local own`i' = `r(mean)'
	
	//house equity
	su parent_house_equity [w = weight_1997] if parent_inc_ventile == `i' & own_house, d
	su parent_house_equity [w = weight_1997] if parent_inc_ventile == `i' & own_house & parent_house_equity<`r(p75)' & parent_house_equity>`r(p25)'
	
	local meanh`i' = log((`r(mean)'^2)/sqrt(`r(mean)'^2 +`r(sd)'^2))
	local sdh`i' = sqrt(log(1 + (`r(sd)'^2)/(`r(mean)'^2)))
	
	//non-housing wealth
	su total_wealth_nonhouse [w = weight_1997] if parent_inc_ventile == `i', d
	su total_wealth_nonhouse [w = weight_1997] if parent_inc_ventile == `i' & total_wealth_nonhouse<`r(p75)' & total_wealth_nonhouse>`r(p25)'
	local meanw`i' = log((`r(mean)'^2)/sqrt(`r(mean)'^2 +`r(sd)'^2))
	local sdw`i' = sqrt(log(1 + (`r(sd)'^2)/(`r(mean)'^2)))
	
	su parent_income [w = weight_1997] if parent_inc_ventile == `i', d
	local pi`i' = `r(p50)'
}

clear
set obs 20
gen v1 = .
gen v2 = .
gen v3 = .
gen v4 = .
gen v5 = .
gen v6 = .

forval i = 1/20{
	replace v1 = `own`i'' in `i'
	replace v2 = `meanh`i'' in `i'
	replace v3 = `sdh`i'' in `i'
	replace v4 = `meanw`i'' in `i'
	replace v5 = `sdw`i'' in `i'
	replace v6 = `pi`i'' in `i'
}

replace v4 = -5 in 2
replace v5 = 0.75 in 2

export delimited "$model/utilities/parent_income_house_dist.csv", replace novarn

clear
set obs 20
gen v1 = .
gen v2 = .
gen v3 = .
forval i = 1/20{
	replace v1 = `pi`i'' in `i'
	replace v2 = `abil_mean_`i'' in `i'
	replace v3 = `abil_sd_`i'' in `i'
}
export delimited "$model/utilities/parent_income_ability_dist.csv", replace novarn



//end of dofile

